const connection = require('../app/database')

class ComplainService {
  async createComplain( content,state ='0',resultId) {
    const statement = `
      INSERT INTO complains(content,state,result_id) VALUES (?,?,?);
    `
    const [res] = await connection.execute(statement,[content,state,resultId])
    return res
  }
  async getComplainList(pagenum = '1', pagesize = '10', state='') {
    const statement = `		
      SELECT (SELECT COUNT(*) FROM complains) totalCount,
      JSON_ARRAYAGG(JSON_OBJECT('id',u.complain_id,'content',u.content,'athleteName',u.athlete_name,'sportName',u.sport_name,'state',u.state,'feedback',u.feedback,'score',u.score,'rank',u.ranking)) list
      FROM	(SELECT c.complain_id,c.content,c.feedback,c.state,aths.score,aths.ranking,s.sport_name,a.athlete_name FROM complains c JOIN
      athletes_sports aths ON aths.id = c.result_id	
      JOIN athletes a ON a.athlete_id = aths.athlete_id
      JOIN sports s ON s.sport_id = aths.sport_id
      WHERE c.state LIKE '%${state}%'
      LIMIT ?,?) u;	
    `
    const offset = (pagenum - 1) * pagesize
    const [result] = await connection.execute(statement, [offset + '', pagesize])
    console.log(result);
    return result[0]
  }
  async resolveComplain(id,feedback) {
    const statement = `
      UPDATE complains SET state = ?,feedback = ?
      WHERE complain_id = ?;
    `
    const [res] = await connection.execute(statement,[1,feedback,id])
    return res
  }
  async removeComplain(id) {
    const statement = `
      DELETE FROM complains WHERE complain_id = ?;
    `
    const [res] = await connection.execute(statement,[id])
    return res
  }

}
module.exports = new ComplainService()